﻿Imports System.Data.SqlClient

Public Class frmFinancialSettings

    Sub FillSettings()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Select * From FinSettings", cnn)
            Dim Reader As SqlDataReader

            Me.GridSettings.Rows.Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            While Reader.Read
                Me.GridSettings.Rows.Add(Reader.Item("Item"), Reader.Item("AccNo"), GetAccName(Reader.Item("AccNo")))
            End While
            cnn.Close()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub

    Private Sub frmSettings_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        FillSettings()
    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand
            Dim Trans As SqlTransaction

            cnn.Open()
            Trans = cnn.BeginTransaction
            cmd.Connection = cnn
            cmd.Transaction = Trans

            'Deleting old values
            cmd.CommandText = "Delete From FinSettings"
            cmd.ExecuteNonQuery()

            'Saving new values 
            For Each row As DataGridViewRow In Me.GridSettings.Rows
                cmd.CommandText = "Insert Into FinSettings (Item,AccNo) Values " & _
                                  "(N'" & row.Cells(Me.DGColItem.Index).Value & "','" & row.Cells(Me.DGColAccNo.Index).Value & "')"
                cmd.ExecuteNonQuery()
            Next

            Trans.Commit()
            cnn.Close()

            MsgBox("Saved Successfully!")

            FillSettings()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub DataGridView1_CellClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles GridSettings.CellClick
        If e.ColumnIndex = Me.DGColSearch.Index Then
            Try
                Dim a As New frmSelectAccount
                a.ShowDialog()

                If SelAccNo <> "" Then
                    Me.Cursor = Cursors.WaitCursor

                    Me.GridSettings.CurrentRow.Cells(Me.DGColAccNo.Index).Value = SelAccNo
                    Me.GridSettings.CurrentRow.Cells(Me.DGColAcc.Index).Value = GetAccName(SelAccNo)

                    Me.Cursor = Cursors.Default
                End If
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub
End Class